package com.property.demo.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel导入导出工具类
 */
public class ExcelUtil {

    /**
     * 导出Excel
     *
     * @param response HTTP响应
     * @param fileName 文件名
     * @param data     数据列表
     * @param headers  表头配置 [{"title": "显示名", "field": "字段名", "width": 宽度}]
     * @throws IOException IO异常
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<?> data, 
                                  List<Map<String, Object>> headers) throws IOException {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        // 创建表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue((String) headers.get(i).get("title"));
            cell.setCellStyle(headerStyle);
            
            // 设置列宽
            if (headers.get(i).containsKey("width")) {
                sheet.setColumnWidth(i, (int) headers.get(i).get("width") * 256);
            } else {
                sheet.setColumnWidth(i, 15 * 256);
            }
        }

        // 填充数据
        int rowNum = 1;
        for (Object obj : data) {
            Row row = sheet.createRow(rowNum++);
            
            for (int i = 0; i < headers.size(); i++) {
                String fieldName = (String) headers.get(i).get("field");
                Cell cell = row.createCell(i);
                
                try {
                    // 使用反射获取字段值
                    Field field = obj.getClass().getDeclaredField(fieldName);
                    field.setAccessible(true);
                    Object value = field.get(obj);
                    
                    if (value != null) {
                        cell.setCellValue(value.toString());
                    }
                } catch (Exception e) {
                    // 处理嵌套对象
                    try {
                        String[] nestedFields = fieldName.split("\\.");
                        Object nestedObj = obj;
                        
                        for (String nested : nestedFields) {
                            Field field = nestedObj.getClass().getDeclaredField(nested);
                            field.setAccessible(true);
                            nestedObj = field.get(nestedObj);
                            
                            if (nestedObj == null) {
                                break;
                            }
                        }
                        
                        if (nestedObj != null) {
                            cell.setCellValue(nestedObj.toString());
                        }
                    } catch (Exception ex) {
                        // 忽略错误
                    }
                }
            }
        }

        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName + ".xlsx");

        // 写入响应
        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
        } finally {
            workbook.close();
        }
    }

    /**
     * 导入Excel
     *
     * @param file    上传的Excel文件
     * @param headers 表头配置 [{"title": "显示名", "field": "字段名"}]
     * @return 解析后的数据列表
     * @throws IOException IO异常
     */
    public static List<Map<String, Object>> importExcel(MultipartFile file, List<Map<String, Object>> headers) throws IOException {
        List<Map<String, Object>> resultList = new ArrayList<>();

        try (InputStream is = file.getInputStream()) {
            Workbook workbook = WorkbookFactory.create(is);
            Sheet sheet = workbook.getSheetAt(0);

            // 获取表头行
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new IOException("Excel文件格式错误，缺少表头");
            }

            // 映射表头与字段
            Map<Integer, String> headerMap = new HashMap<>();
            for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    String headerTitle = cell.getStringCellValue();
                    
                    // 查找对应的字段名
                    for (Map<String, Object> header : headers) {
                        if (headerTitle.equals(header.get("title"))) {
                            headerMap.put(i, (String) header.get("field"));
                            break;
                        }
                    }
                }
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                Map<String, Object> rowData = new HashMap<>();
                boolean hasData = false;

                for (Map.Entry<Integer, String> entry : headerMap.entrySet()) {
                    int colIndex = entry.getKey();
                    String fieldName = entry.getValue();
                    Cell cell = row.getCell(colIndex);
                    
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                rowData.put(fieldName, cell.getStringCellValue());
                                hasData = true;
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    rowData.put(fieldName, cell.getDateCellValue());
                                } else {
                                    rowData.put(fieldName, cell.getNumericCellValue());
                                }
                                hasData = true;
                                break;
                            case BOOLEAN:
                                rowData.put(fieldName, cell.getBooleanCellValue());
                                hasData = true;
                                break;
                            default:
                                rowData.put(fieldName, null);
                        }
                    }
                }

                if (hasData) {
                    resultList.add(rowData);
                }
            }
        }

        return resultList;
    }
} 